The Exact System Information Tool (ESI)
This System Info tool is developed to easily retrieve and adjust Windows, SQL and Database settings to get an optimum performance. It will also automatically send statistical information about Exact tables and index usage to Exact. For this tool to function proper, you need run this tool on the SQL Server using an administrator or equivalent Windows account.
Index
Connect to the local SQL Server Instance
Install and uninstall information
Applying the Recommended Optimizations tab
Supported and tested environments
SQL Maintenance tab
Database Information tab
Exact Data tab
Menu option explained
Options menu
Download the latest version from the following FTP site: ftp://ftpesi.exact.com/ESI/ExactSysInfo.zip
How to use the tool:
Startup Parameter: /AUTO when you start the application with this parameter, it will execute the tool, upload the information and close the tool. This can be used to schedule the ESI tool./DEBUG This will enable the extra debug information (see Options Window)
Step 1: Start and Connect To start the application you need to execute the ExactSysInfo.EXE, see Installation and Uninstall information Establish a connection to the local SQL server instance via Windows or SQL Authentication. See example:
You can send the Improvement Report to the specified e-mail address. (It can take about 10 minutes before the Improvement Report is sent to the provided e-mail address)
Click on the button 'Start Analysis' to start the process. This can take several minutes depending on the number and sizes of the Exact databases. During the analyze process the following information will be displayed, see example:
The Option button provides some settings for the tool, click here for the help about the available Options.
When the process is complete the following information will be displayed, see example: In the Recommendation column, you can see optimized recommendations for the specific items. Note - This tool cannot retrieve information about the disk system (RAID Level) and how many disks are stored in the disk system! The option Start GPedit will only be displayed when the option 'Locked Pages in Memory' is not set correctly. You can adjust this option manually via the link (start the Group Policy Editor) or via this tool in the Recommended Optimization tab.
<< Back
Step 2: Applying the Recommended Optimizations
After the process is complete the number of recommendations will be displayed in the 'Recommended Optimization' tab. See example:
Selected the optimizations that you want to apply to the system (or enable the Select All option) and click on the button: 'Apply Optimizations'. Click the button 'Yes' on the disclaimer popup to apply the recommendations or 'No' ignore the recommendations. After the recommendations are applied a popup might appear to restart your system. It's is advisable to perform the restart of the server of the SQL instance after working hours.
The second window pane shows the general recommendations some which cannot be adjusted by the ESI tool.With the button 'Copy Recommendation to Clipboard' you will copy all values of both panes into the clipboard.
Step 3: SQL Maintenance (not available for Express or MSDE editions) Select the Exact databases for the SQL jobs you want to save. Rebuild Indexes Job This option created a SQL job for the selected database to optimize the indexes for every workday. This will improve the performance by keeping the indexes up-to-date. The default time when the SQL job is executed is 23:00 hours on Monday, Tuesday, Wednesday, Thursday and Friday.Lock Time Out (sec) is by default set to 60 seconds. This means that if an index cannot be rebuild do to locks or other causes, it will be skipped after the selected seconds. Update Statistics Job This option created a SQL job for the selected database to optimize the index statistics. The default time when the SQL job is executed is 01:00 on every Sunday.
With the 'Select Day' option, you can select a different day to start the statics update job.
Optional Optimizations:
With the use of SQL Server 2008 or newer you can implement Filtered Indexes to increase the performance in decrease the size of your indexes. After you selected the database from the dropdown box, a check is performed how many tables do not have filtered indexes. If there are still tables that do not contain the Filtered Indexes, the Implement button will be enabled and you can start the filtered indexes process for the remaining tables.
For more information see the document about Filtered Indexes
Page Compression is only available on SQL Server 2008 Enterprise Edition or newer. It will increase the performance and reduces the size of your database. After you selected a database from the dropdown box, a check is performed if the selected database does not already contain Page Compression. For more information see the document about Page Compression
Create SQL Backup Schedule With this option you can create a SQL job to create a backup of the selected databases. By default, the SQL job will run every working day at 21:00 hours. You can select the folder where the backup file will be stored.
Append / Overwrite (Default Overwrite)With this option you can select if every time the SQL job is executed, the backup will be added to the existing backup file or that the backup file will be overwritten.Keep in mind that using the 'Append' option the size of the backup will can grow very large over time.
Show information or Delete a SQL JobIf you select an option within the 'Installed Optimization Jobs' information will be updated automatically to show which databases are selected, time and day for the selected optimization job.If you want to delete an optimization or backup job created by the ESI Tool, you can just right mouse click on 'Installed Optimization Jobs' can click on the Delete option.
Job History If you select an option within the 'Installed Optimization Jobs' the Job History for the selected Job will automatically displayed in the Job History table.
Tab: Database Information Information about your SQL databases are shown in two panels, the first panel shows the most important settings of all the databases. The second panel displays information about the size, growth and location of the database files.
TAB: Exact Databases The Exact Databases tab displays a result set of Exact specific related information about Error Logs, index information and statistical table information. This information is sent to the Exact FTP server.
The Menu
Hamburger Menu
* SQL ProcessWith the new SQL Processes tab you can easily check which process/user/application is causing high CPU, Physical_IO (disk) or Memory usage using the 'Current' activity. If you select the 'Historic' activity your can find the top 50 previously executed worse performing queries since the last restart of SQL. For both views you can click on the header of a column to change the sorting order. It also possible to drilldown to a specific database by simple selecting a database and click on the Refresh button.By default the SQL Processes tab is not visible, you need to enable the tab by selecting SQL Processes in the hamburger menu - View.
** Performance MonitorYou can quickly check the several performance monitor counters on the Performance Monitor tab.Just select the Disk System where the SQL databases resides on and click on the Start button.By default the option: 'Interval Time (ms)' is set to 1000, so every second the counters are checked. You can stop the process by clicking on the Stop button or wait until the Set End Date/Time is reached (default 18:00 hour of the current day).With the option 'Enable Logging' enabled, a file is created with the name ESI_PerfMon_[current date].LOG containing date and time of a counter that reached the threshold.
Use the Windows Performance Monitor to really analyze the performance of the system over a longer period.By default the Performance Monitor tab is not visible, you need to enable the tab by selecting Performance Monitor in the hamburger menu - View.
With the Options button you can select the appropriate options before starting the analyses or leave every setting to de the default.
Program Setting
Calculate Recommended Hardware
Option 'Based on Largest Database' will calculate the recommendation based on the size of only the largest SQL database. Other SQL databases will not be taken in account. This option is useful when u use 1 database most of the time.
Option 'Based on Total Size of All User Databases' will calculate the recommendation based on all user SQL databases. This option is useful when you use multiple SQL databases most of the time
E-mail Settings
SMTP server is only need if you want to e-mail the results (html file) directly from this application. The default port for SMTP is 25, but this can differ. SMTP Hint will fill in a suggestion when might be the SMTP server of your company. Ask your administrator for the correct SMTP address of your mail server.
Installation and Uninstall Information: The Exact System Information Tool needs the Microsoft .NET Framework 4.5.2 to run.
To install the application, unzip the ExactSysInfo.zip file to a folder on a hard drive. The zip contains 6 files, the ExactSysInfo.exe, ESI_Exact.SQL, ESI_AutoUpdater.exe, NTRights.exe, Page_Compress.sql and ImplementFilteredIndexesGlobe.sql Uninstall the application
To uninstall or remove all extracted files from the ExactSysInfo.zip file. To remove the registry entries created by this application, you can execute the attached: 'Remove registry setting of the ESI tool.reg'
Supported and tested environments: (x64 platforms)Windows 8, Windows 10Windows Server 2012 (R2), 2016 and 2019 Microsoft SQL Server 2012, 2014, 2016, 2017 and 2019Microsoft Azure
Disclaimer: In article 4.5 of the Customer License Agreement states that the license-holder is responsible for the installation and configuration of the software on his system. The license-holder is responsible for adjustments made to the system with the use of the Exact System Information Tool.